#!/bin/bash

#只需要重新计算上个月1日至今的数据
#将origin_type来源渠道字段转换为线上/线下，如果origin_type是NETSERVICE和PRESIGNUP类型，即为1线上，否则为0线下。

#! /bin/bash
SQOOP_HOME=/usr/bin/sqoop

#上个月1日
Last_Month_DATE=$(date -d "-1 month" +%Y-%m-01)

${HIVE_HOME} -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--插入数据
insert into table itcast_dwm.itcast_intention_dwm partition (yearinfo,monthinfo,dayinfo)
select
    dwd.customer_id,
    dwd.create_date_time,
    cus.area,
    dwd.itcast_school_id,
    sch.name as itcast_school_name,
    dwd.deleted,
    dwd.origin_type,
    dwd.itcast_subject_id,
    sub.name as itcast_subject_name,
    dwd.hourinfo,
    dwd.origin_type_stat,
    if(clue.clue_state='VALID_NEW_CLUES', '1', if(clue.clue_state='VALID_PUBLIC_NEW_CLUE', '0', '-1')) as clue_state_stat,
    e.department_id as tdepart_id,
    dept.name as tdepart_name,
    dwd.yearinfo,
    dwd.monthinfo,
    dwd.dayinfo
from itcast_dwd.itcast_intention_dwd dwd
left join itcast_ods.customer_clue clue on clue.customer_relationship_id=dwd.rid
left join itcast_dimen.customer cus on dwd.customer_id = cus.id
left join itcast_dimen.employee e on dwd.creator = e.id
left join itcast_dimen.scrm_department dept on e.department_id = dept.id
left join itcast_dimen.itcast_subject sub on dwd.itcast_subject_id = sub.id
left join itcast_dimen.itcast_school sch on dwd.itcast_school_id = sch.id
where concat_ws('-',dwd.yearinfo,dwd.monthinfo,dwd.dayinfo) >= '${Last_Month_DATE}'--2020-12-01;
"
